'''
数据分析：
    1.统计表格中有多少人
    2.统计电信、联通、移动用户数量占比
    3.总公司男女人数
    4.年龄超过45岁的老员工人数
    5.薪资高于8000人数和低于3000人数
    6.统计去传媒公司工作的人数
    7.统计可能在疫情高危地区的人数（黑龙江、北京、福建、四川）
'''



import xlrd
import openpyxl

wb = xlrd.open_workbook(filename=r"baidu-员工的人员信息.xls")
sheet = wb.sheet_by_index(0)

print("总共：",sheet.nrows,"个人")


row = sheet.row_values(4)


cucc=['130','131','132','145','155','156','175','176','185','186','171','1704','1707','1708','1709']
cmcc=['134','135','136','137','138','139','147','150','151','152','158','159','157','178','182','183','184','187','188','1703','1705','1706']
cdma=['133','149','153','173','177','180','181','189','1700','1701','1702']
# #读取某一列的数据
cum=0
mum=0
dum=0
col = sheet.col_values(5)
for number in col:
    for a in cucc:
        if a==number[0:3]or a==number[0:4]:
            cum=cum+1

for number in col:
    for b in cmcc:
        if b == number[0:3]or b==number[0:4]:
            mum=mum+1

for number in col:
     for c in cdma:
        if c == number[0:3]or c==number[0:4]:
            dum = dum + 1
cb=cum+mum+dum
cb1=(cum/cb)*100
cb2=(mum/cb)*100
cb3=(dum/cb)*100
print('中国联通占比为百分之'+str(cb1)+"  中国移动占比为百分之"+str(cb2)+'  中国电信占比为百分之'+str(cb3))

man=0
woman=0
sex=sheet.col_values(8)
for se in sex:
    if se=='男':
        man=man+1
    else:
        woman=woman+1
print('总公司的男性有'+str(man)+"总公司的女性有"+str(woman))


old=0
age=sheet.col_values(7)
for a in age:
    if int(a)>=45:
        old=old+1
print('年龄超过45岁的老员工人数为'+str(old))


monkey_max=0
monkey_min=0
monkey_all=sheet.col_values(11)

for m in monkey_all:
    if int(m)>8000:
        monkey_max=monkey_max+1
    elif int(m)<3000:
        monkey_min=monkey_min+1
monkey1=monkey_max+monkey_min
print('工资高于8000元的员工数量为'+str(monkey_max)+'工资低于3000元的员工数量为'+str(monkey_min)+'一共员工的数量为'+str(monkey1))

lo_per=0
lo_place=sheet.col_values(13)
for l in lo_place:
    if '传媒' in l:
        lo_per=lo_per+1
print('去传媒公司工作的人员数量为'+str(lo_per))

address_all=sheet.col_values(9)
address_dar=0
for ad in address_all:
    if '黑龙江' in ad or '北京'in ad or '福建'in ad or '四川'in ad:
        address_dar=address_dar+1
print('可能在高危区域的人数为'+str(address_dar))
# #读取特定单元格的数据
cell = sheet.cell_value(4,5)









